package www.lagou.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import www.lagou.entity.Dept;
import www.lagou.entity.Employee;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 查询指定的员工信息。
 */
public class EmployeeQuery {
    // 用到的 SQL 语句。
    private static final String SQL_GET_ALL_EMPLOYEES = "SELECT id, `name`, age, sex, salary, empdate, did FROM employee WHERE did IS NOT NULL";
    private static final String SQL_GET_USED_DEPTS = "SELECT id, deptname FROM dept WHERE id IN (SELECT did FROM employee)";
    private static final String SQL_GET_PARTIAL_EMPLOYEES_EMP = "SELECT `name`, salary, did FROM employee";

    /**
     * 查询所有的员工信息 (不包含没有部门的员工)。
     *
     * @return 员工信息列表。
     * @throws SQLException 数据库查询错误。
     */
    public static List<Employee> getAllEmployees() throws SQLException {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

        // 查询员工和部门。
        List<Employee> emps = runner.query(SQL_GET_ALL_EMPLOYEES, new BeanListHandler<>(Employee.class));
        Map<Integer, Dept> deptTable = getUsedDepts();

        // 手动连表。
        for (Employee emp : emps) {
            Dept dept = deptTable.get(emp.getDid());
            emp.setDept(dept);
        }
        return emps;
    }

    /**
     * 查询每个员工的姓名, 薪资和所属部门名称。
     *
     * @return 员工信息列表。
     * @throws SQLException 数据库查询错误。
     */
    public static List<Employee> getPartialEmployees() throws SQLException {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

        // 查询员工和部门。
        List<Employee> emps = runner.query(SQL_GET_PARTIAL_EMPLOYEES_EMP, new BeanListHandler<>(Employee.class));
        Map<Integer, Dept> deptTable = getUsedDepts();

        // 手动连表。
        for (Employee emp : emps) {
            Dept dept = deptTable.get(emp.getDid());
            emp.setDept(dept);
        }
        return emps;
    }

    /**
     * 查询员工信息列表中涉及到的部门信息。
     *
     * @return 部门列表。
     * @throws SQLException 数据库查询错误。
     */
    private static Map<Integer, Dept> getUsedDepts() throws SQLException {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());

        List<Dept> depts = runner.query(SQL_GET_USED_DEPTS, new BeanListHandler<>(Dept.class));
        Map<Integer, Dept> deptTable = new HashMap<>();
        for (Dept dept : depts) {
            deptTable.put(dept.getId(), dept);
        }
        return deptTable;
    }
}
